﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebSite.App_Code;
using WebSite.App_Data;
using Excel = Microsoft.Office.Interop.Excel;
using System.Web.Configuration;
using System.IO;
using WebSite.Models;

namespace WebSite.Controllers
{
    public class BaseController : Controller
    {
        public void CurrentUserClear()
        {
            Session.Remove("Global.User");
        }

        protected override void OnAuthorization(AuthorizationContext filterContext)
        {
            String UrlPath = String.Format("/{0}/{1}",
                filterContext.RequestContext.RouteData.Values["controller"],
                filterContext.RequestContext.RouteData.Values["action"]);

            if (UrlPath != "/Home/Error" && UrlPath != "/Home/Login")
            {
                DBDataContext DB = new DBDataContext();

                DB.Connection.Open();

                IEnumerable<md_Page> ieCheck = from x in DB.md_Pages
                                               where x.PageUrl == UrlPath
                                               where x.NeedCheck
                                               select x;

                DB.Connection.Close();

                if (ieCheck.Any())
                {
                    if (CurrentUser != null)
                    {
                        if (CurrentUser.PageList.Where(o => o.PageUrl == UrlPath).Any())
                            ViewData["Global.PathTitle"] = CurrentUser.PageList.Where(o => o.PageUrl == UrlPath).First().PageTitle;
                        else
                        {
                            throw new Exception("没有权限");
                            //filterContext.RequestContext.HttpContext.Response.Redirect("/Home/Login");
                        }
                    }
                    else
                        throw new Exception("长时间未操作，请重新登录");
                    //filterContext.RequestContext.HttpContext.Response.Redirect("/Home/Login");
                }
            }

            base.OnAuthorization(filterContext);
        }

        protected override void OnException(ExceptionContext filterContext)
        {
            Exception ex = null;

            String UrlPath = String.Format("/{0}/{1}",
                filterContext.RequestContext.RouteData.Values["controller"],
                filterContext.RequestContext.RouteData.Values["action"]);

            CommonItem.WirteErrorLog(L_LogType.Other, filterContext.Exception, "[BaseCotroller.OnException]", this.CurrentUser);

            if (UrlPath != "/Home/Error")
            {
                ex = filterContext.Exception;

                //if (ex != null)
                //    Session["Global.Error"] = ex;
                
                filterContext.RequestContext.HttpContext.Response.Redirect("~/Home/Error/");
            }

            base.OnException(filterContext);

            if (ex != null)
                Session["Global.Error"] = ex;
        }

        /// <summary>
        /// 校验页面访问权限
        /// </summary>
        public void CheckRight_Page()
        {

        }

        public String CurrentYear
        {
            get
            {
                if (Session["Global.CurrentYear"] == null) {

                    DBDataContext DB = new DBDataContext();
                    DB.Connection.Open();
                    IEnumerable<list_Year> ieYear = from x in DB.list_Years where x.IsUsed select x;
                    if (ieYear.Any()) { Session["Global.CurrentYear"] = ieYear.First().ID; }
                    DB.Connection.Close();

                }

                return Session["Global.CurrentYear"].ToString();
            }
            set
            {
                Session["Global.CurrentYear"] = value;
            }
        }

        public UserItem CurrentUser
        {
            get
            {
                if (Session == null) { return new UserItem(); }

                if (Session["Global.User"] == null) { return new UserItem(); }

                return (UserItem)Session["Global.User"];
            }
            set
            {
                Session["Global.User"] = value;
            }
        }

        public Exception Error
        {
            get
            {
                if (Session == null) { return new Exception(); }

                if (Session["Global.Error"] == null) { return new Exception(); }

                return (Exception)Session["Global.Error"];
            }
            set
            {
                Session["Global.Error"] = value;
            }
        }

        public Dictionary<String,String> PageList
        {
            get
            {
                if (Session["Global.PageList"] == null) {

                    Dictionary<String, String> init = new Dictionary<string, string>();
                    
                    Session["Global.PageList"] = init;
                }

                return (Dictionary<String, String>)Session["Global.PageList"];
            }
        }

        public void WirteLog(L_LogType Type, String Key, String LogTxt) {

            DBDataContext DB = new DBDataContext();

            DB.Connection.Open();

            CommonItem.WirteLog(DB, Type, LogTxt, Key, String.Empty, this.CurrentUser);

            DB.SubmitChanges();

            DB.Connection.Close();
        }

        public ActionResult DownloadTemplate(int ProjectCategory, List<BudgetItem> lsTmp, Boolean CanEdit)
        {
            return DownloadTemplate(ProjectCategory, lsTmp, true, false, CanEdit);
        }

        public ActionResult DownloadTemplate(int ProjectCategory, String Ids, Boolean CanEdit, Char Split)
        {
            NormalBudgetUploadModel M = new NormalBudgetUploadModel();
            M.U = this.CurrentUser;

            List<BudgetItem> lsTmp = M.GetCurrentBudget(Ids, Split);

            return DownloadTemplate(ProjectCategory, lsTmp, true, false, CanEdit);
        }

        public ActionResult DownloadTemplate(int ProjectCategory, List<BudgetItem> lsThis, Boolean IsDelete, Boolean IsCopy, Boolean CanEdit)
        {
            Dictionary<int, String> dicSourcePath = new Dictionary<int, string>();

            if (IsCopy)
            {
                dicSourcePath.Add(1, "XLS--预算科研项目[1].xls");
                dicSourcePath.Add(2, "XLS--预算外科研项目[1].xls");
                dicSourcePath.Add(3, "XLS--有偿服务项目[1].xls");
            }
            else
            {
                dicSourcePath.Add(1, "XLS--预算科研项目.xls");
                dicSourcePath.Add(2, "XLS--预算外科研项目.xls");
                dicSourcePath.Add(3, "XLS--有偿服务项目.xls");
            }

            String strTempPath = Server.MapPath(WebConfigurationManager.AppSettings["DownloadTmpPath"]) + Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            System.IO.File.Copy(Server.MapPath(WebConfigurationManager.AppSettings["TemplatePath"]) + dicSourcePath[ProjectCategory], strTempPath);

            #region Get Data From DB

            DBDataContext DB = new DBDataContext();
            DB.Connection.Open();

            IEnumerable<md_User> ieUserAll = from x in DB.md_Users where x.Status select x;

            DB.Connection.Close();

            #endregion

            Excel._Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet ws = null;
            ExcelUilityItem XlTools = new ExcelUilityItem();

            try
            {
                if (lsThis.Count > 0)
                {
                    Dictionary<String, String> lsYesOrNo = ExcelSheetItem.GetList_YN();
                    Dictionary<String, String> lsAdvenceStatus = ExcelSheetItem.GetList_AdvenceStatus();

                    app = new Excel.ApplicationClass();

                    app.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
                    app.DisplayAlerts = false;
                    book = app.Workbooks.Open(strTempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    
                    book.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                    IEnumerator<BudgetItem> oThis = lsThis.GetEnumerator();

                    if (IsCopy)
                    {
                        for (int i = 1; i < lsThis.Count; i++)
                        {
                            ws = (Excel.Worksheet)book.Worksheets.get_Item("00");
                            ws.Copy(Type.Missing, ws);
                        }
                    }

                    for (int i = 1; i <= book.Worksheets.Count; i++)
                    {
                        ws = (Excel.Worksheet)book.Worksheets.get_Item(i);

                        if (ws.Name.ToLower() == "welcome" || ws.Name.ToLower() == "var")
                            continue;

                        if (oThis.MoveNext())
                        {
                            if (ProjectCategory == 1)
                            {
                                #region 预算科研项目

                                ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                                oThis.Current.C.Calculate(oThis.Current.Budget.Guid);
                                                                
                                ws.get_Range("N1").Value2 = oThis.Current.C.abcde_lj - oThis.Current.C.abcde;
                                ws.get_Range("N2").Value2 = oThis.Current.C.x_lj - oThis.Current.C.x;

                                ws.get_Range("B1").Value = oThis.Current.Budget.Year;
                                ws.get_Range("B2").Value = oThis.Current.Project.Department.DepartmentName;
                                TemplateSheetItem.SetExcelCellValue(ws, "B3:H3", oThis.Current.Project.Project.ProjectName, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B4", oThis.Current.Project.Project.ProjectCode, false);
                                TemplateSheetItem.SetExcelCellValue(ws, "D4", oThis.Current.Budget.AnnualProgress, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "G4:H4", oThis.Current.Project.Project.FundsSources, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B5", CommonItem.X_yyyyMMdd(oThis.Current.Project.Project.ProjectBeginTime), CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "D5", CommonItem.X_yyyyMMdd(oThis.Current.Project.Project.ProjectEndTime), CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "F5", oThis.Current.Project.ProjcetPIC.UserName, CanEdit);

                                if (!String.IsNullOrEmpty(oThis.Current.Budget.IsSoft) && lsYesOrNo.Keys.Contains(oThis.Current.Budget.IsSoft))
                                    TemplateSheetItem.SetExcelCellValue(ws, "H5", lsYesOrNo[oThis.Current.Budget.IsSoft], CanEdit);

                                if (!String.IsNullOrEmpty(oThis.Current.Budget.IsAdvence) && lsYesOrNo.Keys.Contains(oThis.Current.Budget.IsAdvence))
                                    ws.get_Range("A7").Value = lsYesOrNo[oThis.Current.Budget.IsAdvence];
                                if (oThis.Current.C.IsA.Value.ToUpper() == "TRUE")
                                {
                                    ws.get_Range("B7").Value2 = oThis.Current.Budget.AdvencePlanAmount;
                                    ws.get_Range("C7").Value2 = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvencePlanTime);
                                    ws.get_Range("D7").Value = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvenceReturnTime);
                                    ws.get_Range("E7").Value = oThis.Current.AdvencePIC.UserName;
                                    ws.get_Range("F7").Value = oThis.Current.C.x;
                                    ws.get_Range("G7").Value = oThis.Current.Budget.AdvencetRemark;

                                    ws.get_Range("A7").Locked = true;
                                    ws.get_Range("A7").Interior.ColorIndex = 15;
                                    ws.get_Range("A7").Validation.Delete();

                                    ws.get_Range("B7:H7").Locked = false;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("A10").Formula = "=SUM(F7,D8,H8)";
                                }
                                else
                                {
                                    ws.get_Range("A7").Locked = false;
                                    ws.get_Range("A7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B7:H7").Locked = true;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = 15;

                                    ws.get_Range("A10").Formula = "=SUM(B10:F10)";
                                }                                

                                XlTools.AddList(ref ws, "E7"
                                    , (from x in ieUserAll where x.DepartmentGuid == oThis.Current.Project.Project.DepartmentGuid select x.UserName)
                                    , "请选择正确的负责人");

                                ws.get_Range("B8").Value = oThis.Current.C.AS.Name;
                                ws.get_Range("D8").Value = oThis.Current.C.y;
                                ws.get_Range("F8").Value = oThis.Current.C.z;
                                ws.get_Range("H8").Value = oThis.Current.C.f;

                                ws.get_Range("B10:F10").Locked = true;
                                ws.get_Range("B10:F10").Interior.ColorIndex = 15;
                                ws.get_Range("B10").Value = oThis.Current.Budget.Income_StandardFunds;
                                ws.get_Range("C10").Value = oThis.Current.Budget.Income_ProjectFunds;
                                ws.get_Range("D10").Value = oThis.Current.Budget.Income_Subvention;
                                ws.get_Range("E10").Value = oThis.Current.Budget.Income_Other;
                                ws.get_Range("F10").Value = oThis.Current.Budget.Income_Balance;

                                ws.get_Range("G10").Value = oThis.Current.Budget.BudgetRemark;

                                ws.get_Range("G12").Value = oThis.Current.Budget.BudgetPay_Remark;

                                ws.get_Range("B13").Value = oThis.Current.Budget.His_Amount;
                                ws.get_Range("D13").Value = oThis.Current.Budget.His_Remark;
                                ws.get_Range("F13").Value = oThis.Current.Budget.ActualPay_Amount;
                                ws.get_Range("H13").Value = oThis.Current.Budget.ActualPay_Remark;

                                ws.get_Range("A15").Value = oThis.Current.Budget.His_Amount_Equipment;
                                ws.get_Range("B15").Value = oThis.Current.Budget.His_Amount_Material;
                                ws.get_Range("C15").Value = oThis.Current.Budget.His_Amount_Coordination;
                                ws.get_Range("D15").Value = oThis.Current.Budget.His_Amount_Bussiness;
                                ws.get_Range("E15").Value = oThis.Current.Budget.ActualPay_Amount_Equipment;
                                ws.get_Range("F15").Value = oThis.Current.Budget.ActualPay_Amount_Material;
                                ws.get_Range("G15").Value = oThis.Current.Budget.ActualPay_Amount_Coordination;
                                ws.get_Range("H15").Value = oThis.Current.Budget.ActualPay_Amount_Bussiness;

                                if (CanEdit)
                                {
                                    ws.get_Range("D8").Locked = false;
                                    ws.get_Range("D8").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B10:F10").Locked = false;
                                    ws.get_Range("B10:F10").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
                                }

                                ws.Name = oThis.Current.Budget.Year + " " + oThis.Current.Project.Project.ProjectCode;

                                #endregion
                            }

                            if (ProjectCategory == 2)
                            {
                                #region 预算外科研项目

                                ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                                oThis.Current.C.Calculate(oThis.Current.Budget.Guid);

                                ws.get_Range("N1").Value2 = oThis.Current.C.abcde_lj - oThis.Current.C.abcde;
                                ws.get_Range("N2").Value2 = oThis.Current.C.x_lj - oThis.Current.C.x;

                                ws.get_Range("B1").Value = oThis.Current.Budget.Year;
                                ws.get_Range("B2:G2").Value = oThis.Current.Project.Department.DepartmentName;
                                TemplateSheetItem.SetExcelCellValue(ws, "B3:H3", oThis.Current.Project.Project.ProjectName, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B4", oThis.Current.Project.Project.ProjectCode, false);
                                TemplateSheetItem.SetExcelCellValue(ws, "D4", oThis.Current.Budget.AnnualProgress, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "G4:H4", oThis.Current.Project.Project.FundsSources, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B5", CommonItem.X_yyyyMMdd(oThis.Current.Project.Project.ProjectBeginTime), CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "D5", CommonItem.X_yyyyMMdd(oThis.Current.Project.Project.ProjectEndTime), CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "F5", oThis.Current.Project.ProjcetPIC.UserName, CanEdit);

                                if (!String.IsNullOrEmpty(oThis.Current.Budget.IsSoft) && lsYesOrNo.Keys.Contains(oThis.Current.Budget.IsSoft))
                                    TemplateSheetItem.SetExcelCellValue(ws, "H5", lsYesOrNo[oThis.Current.Budget.IsSoft], CanEdit);

                                if (!String.IsNullOrEmpty(oThis.Current.Budget.IsAdvence) && lsYesOrNo.Keys.Contains(oThis.Current.Budget.IsAdvence))
                                    ws.get_Range("A7").Value = lsYesOrNo[oThis.Current.Budget.IsAdvence];

                                if (oThis.Current.C.IsA.Value.ToUpper() == "TRUE")
                                {
                                    ws.get_Range("B7").Value2 = oThis.Current.Budget.AdvencePlanAmount;
                                    ws.get_Range("C7").Value2 = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvencePlanTime);
                                    ws.get_Range("D7").Value = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvenceReturnTime);
                                    ws.get_Range("E7").Value = oThis.Current.AdvencePIC.UserName;
                                    ws.get_Range("F7").Value = oThis.Current.C.x;
                                    ws.get_Range("G7").Value = oThis.Current.Budget.AdvencetRemark;

                                    ws.get_Range("A7").Locked = true;
                                    ws.get_Range("A7").Interior.ColorIndex = 15;
                                    ws.get_Range("A7").Validation.Delete();

                                    ws.get_Range("B7:H7").Locked = false;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("A10").Formula = "=SUM(F7,D8,H8)";
                                }
                                else
                                {
                                    ws.get_Range("A7").Locked = false;
                                    ws.get_Range("A7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B7:H7").Locked = true;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = 15;

                                    ws.get_Range("A10").Formula = "=SUM(B10:F10)";
                                }

                                XlTools.AddList(ref ws, "E7"
                                    , (from x in ieUserAll where x.DepartmentGuid == oThis.Current.Project.Project.DepartmentGuid select x.UserName)
                                    , "请选择正确的负责人");

                                ws.get_Range("B8").Value = oThis.Current.C.AS.Name;
                                ws.get_Range("D8").Value = oThis.Current.C.y;
                                ws.get_Range("F8").Value = oThis.Current.C.z;
                                ws.get_Range("H8").Value = oThis.Current.C.f;

                                ws.get_Range("B10:F10").Locked = true;
                                ws.get_Range("B10:F10").Interior.ColorIndex = 15;
                                ws.get_Range("B10").Value = oThis.Current.Budget.Income_StandardFunds;
                                ws.get_Range("C10").Value = oThis.Current.Budget.Income_ProjectFunds;
                                ws.get_Range("D10").Value = oThis.Current.Budget.Income_Subvention;
                                ws.get_Range("E10").Value = oThis.Current.Budget.Income_Other;
                                ws.get_Range("F10").Value = oThis.Current.Budget.Income_Balance;

                                ws.get_Range("G10").Value = oThis.Current.Budget.BudgetRemark;

                                ws.get_Range("B14").Value = oThis.Current.Budget.His_Amount_Equipment;
                                ws.get_Range("C14").Value = oThis.Current.Budget.His_Amount_Material;
                                ws.get_Range("D14").Value = oThis.Current.Budget.His_Amount_Coordination;
                                ws.get_Range("E14").Value = oThis.Current.Budget.His_Amount_Bussiness;
                                ws.get_Range("F14").Value = oThis.Current.Budget.His_Amount_Charge;
                                ws.get_Range("G14:H14").Value = oThis.Current.Budget.His_Remark;

                                ws.get_Range("B16").Value = oThis.Current.Budget.BudgetPay_Amount_Equipment;
                                ws.get_Range("C16").Value = oThis.Current.Budget.BudgetPay_Amount_Material;
                                ws.get_Range("D16").Value = oThis.Current.Budget.BudgetPay_Amount_Coordination;
                                ws.get_Range("E16").Value = oThis.Current.Budget.BudgetPay_Amount_Bussiness;
                                ws.get_Range("F16").Value = oThis.Current.Budget.BudgetPay_Amount_Charge;
                                ws.get_Range("G16:H16").Value = oThis.Current.Budget.BudgetPay_Remark;

                                Int32 BudgetFundsRow = Convert.ToInt32(app.WorksheetFunction.Match(9999, ws.get_Range("N:N"), 0)) + 1;

                                ws.get_Range("F" + BudgetFundsRow.ToString()).Value = oThis.Current.Budget.BudgetFunds;

                                if (CanEdit)
                                {
                                    ws.get_Range("D8").Locked = false;
                                    ws.get_Range("D8").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B10:F10").Locked = false;
                                    ws.get_Range("B10:F10").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
                                }

                                ws.Name = oThis.Current.Budget.Year + " " + oThis.Current.Project.Project.ProjectCode;

                                #endregion
                            }

                            if (ProjectCategory == 3)
                            {
                                #region 有偿服务项目

                                ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                                oThis.Current.C.Calculate(oThis.Current.Budget.Guid);

                                ws.get_Range("M1").Value2 = oThis.Current.C.abcde_lj - oThis.Current.C.abcde;
                                ws.get_Range("M2").Value2 = oThis.Current.C.x_lj - oThis.Current.C.x;

                                ws.get_Range("B1").Value = oThis.Current.Budget.Year;
                                ws.get_Range("B2:G2").Value = oThis.Current.Project.Department.DepartmentName;
                                TemplateSheetItem.SetExcelCellValue(ws, "B3:H3", oThis.Current.Project.Project.ProjectName, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B4", oThis.Current.Project.Project.ProjectCode, false);
                                TemplateSheetItem.SetExcelCellValue(ws, "D4", oThis.Current.Budget.AnnualProgress, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "G4:H4", oThis.Current.Project.Project.FundsSources, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "B5", oThis.Current.Project.Project.ProjectBeginTime, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "D5", oThis.Current.Project.Project.ProjectEndTime, CanEdit);
                                TemplateSheetItem.SetExcelCellValue(ws, "F5", oThis.Current.Project.ProjcetPIC.UserName, CanEdit);

                                if (!String.IsNullOrEmpty(oThis.Current.Budget.IsAdvence) && lsYesOrNo.Keys.Contains(oThis.Current.Budget.IsAdvence))
                                    ws.get_Range("A7").Value = lsYesOrNo[oThis.Current.Budget.IsAdvence];

                                if (oThis.Current.C.IsA.Value.ToUpper() == "TRUE")
                                {
                                    ws.get_Range("B7").Value2 = oThis.Current.Budget.AdvencePlanAmount;
                                    ws.get_Range("C7").Value2 = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvencePlanTime);
                                    ws.get_Range("D7").Value = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvenceReturnTime);
                                    ws.get_Range("E7").Value = oThis.Current.AdvencePIC.UserName;
                                    ws.get_Range("F7").Value = oThis.Current.C.x;
                                    ws.get_Range("G7").Value = oThis.Current.Budget.AdvencetRemark;

                                    ws.get_Range("A7").Locked = true;
                                    ws.get_Range("A7").Interior.ColorIndex = 15;
                                    ws.get_Range("A7").Validation.Delete();

                                    ws.get_Range("B7:H7").Locked = false;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("A10").Formula = "=SUM(F7,D8,H8)";
                                }
                                else
                                {
                                    ws.get_Range("A7").Locked = false;
                                    ws.get_Range("A7").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B7:H7").Locked = true;
                                    ws.get_Range("B7:H7").Interior.ColorIndex = 15;

                                    ws.get_Range("A10").Formula = "=SUM(B10:F10)";
                                }

                                XlTools.AddList(ref ws, "E7"
                                    , (from x in ieUserAll where x.DepartmentGuid == oThis.Current.Project.Project.DepartmentGuid select x.UserName)
                                    , "请选择正确的负责人");

                                ws.get_Range("B8").Value = oThis.Current.C.AS.Name;
                                ws.get_Range("D8").Value = oThis.Current.C.y;
                                ws.get_Range("F8").Value = oThis.Current.C.z;
                                ws.get_Range("H8").Value = oThis.Current.C.f;

                                ws.get_Range("B10:F10").Locked = true;
                                ws.get_Range("B10:F10").Interior.ColorIndex = 15;

                                ws.get_Range("B10").Value = oThis.Current.Budget.Income_StandardFunds;
                                ws.get_Range("C10").Value = oThis.Current.Budget.Income_ProjectFunds;
                                ws.get_Range("D10").Value = oThis.Current.Budget.Income_Subvention;
                                ws.get_Range("E10").Value = oThis.Current.Budget.Income_Other;
                                ws.get_Range("F10").Value = oThis.Current.Budget.Income_Balance;
                                ws.get_Range("G10:H10").Value = oThis.Current.Budget.BudgetRemark;

                                //ws.get_Range("A12").Value = oThis.Current.Budget.BudgetPay_Amount;
                                //ws.get_Range("B12").Value = oThis.Current.Budget.BudgetPay_Amount_Prime;
                                //ws.get_Range("C12").Value = oThis.Current.Budget.BudgetPay_Amount_Public;
                                //ws.get_Range("D12").Value = oThis.Current.Budget.BudgetPay_Amount_Charge;
                                //ws.get_Range("E12").Value = oThis.Current.Budget.BudgetPay_Amount_Budget;
                                //ws.get_Range("F12").Value = oThis.Current.Budget.BudgetPay_Amount_Profit;
                                ws.get_Range("G12:H12").Value = oThis.Current.Budget.BudgetPay_Remark;

                                ws.get_Range("A14").Value = oThis.Current.Budget.His_Amount;
                                ws.get_Range("B14").Value = oThis.Current.Budget.His_Amount_Prime;
                                ws.get_Range("C14").Value = oThis.Current.Budget.His_Amount_Public;
                                ws.get_Range("D14").Value = oThis.Current.Budget.His_Amount_Charge;

                                ws.get_Range("E14").Value = oThis.Current.Budget.ActualPay_Amount;
                                ws.get_Range("F14").Value = oThis.Current.Budget.ActualPay_Amount_Prime;
                                ws.get_Range("G14").Value = oThis.Current.Budget.ActualPay_Amount_Public;
                                ws.get_Range("H14").Value = oThis.Current.Budget.ActualPay_Amount_Charge;

                                Int32 BudgetFundsRow = Convert.ToInt32(app.WorksheetFunction.Match(9999, ws.get_Range("N:N"), 0)) + 1;

                                ws.get_Range("F" + BudgetFundsRow.ToString()).Value = oThis.Current.Budget.BudgetFunds;

                                if (CanEdit)
                                {
                                    ws.get_Range("D8").Locked = false;
                                    ws.get_Range("D8").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                    ws.get_Range("B10:F10").Locked = false;
                                    ws.get_Range("B10:F10").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
                                }

                                ws.Name = oThis.Current.Budget.Year + " " + oThis.Current.Project.Project.ProjectCode;

                                #endregion
                            }

                            List<String> Lv3_ID = new List<string>();

                            foreach (wf_BudgetItem Item in oThis.Current.ItemList)
                            {
                                if (Item.ItemLevel == 2 && app.WorksheetFunction.CountIf(ws.get_Range("N:N"), Item.ItemID) == 1)
                                {
                                    Int32 tmp_row_id = Convert.ToInt32(app.WorksheetFunction.Match(Convert.ToInt32(Item.ItemID), ws.get_Range("N:N")));

                                    ws.get_Range("F" + tmp_row_id.ToString()).Value2 = Item.ItemAmount;
                                    ws.get_Range("H" + tmp_row_id.ToString()).Value2 = Item.ItemRem;
                                }

                                if (Item.ItemLevel == 3)
                                {
                                    if (Lv3_ID.Contains(Item.ItemID))
                                        continue;
                                    else
                                    {
                                        Int32 lv3_count = Convert.ToInt32(app.WorksheetFunction.CountIf(ws.get_Range("N:N"), Item.ItemID));
                                        Int32 lv3_min = Convert.ToInt32(app.WorksheetFunction.Match(Convert.ToInt32(Item.ItemID), ws.get_Range("N:N"), 0));
                                        Int32 lv3_max = Convert.ToInt32(app.WorksheetFunction.Match(Convert.ToInt32(Item.ItemID), ws.get_Range("N:N"), 1));
                                        Int32 lv3_count_ = oThis.Current.ItemList.Count(o => o.ItemID == Item.ItemID);
                                        Int32 lv3_x = (lv3_count > lv3_count_) ? 0 : (lv3_count_ - lv3_count);

                                        if (lv3_x > 0)
                                        {
                                            ws.get_Range("N" + (lv3_min + 2).ToString()).EntireRow.Copy();
                                            ws.get_Range("N" + (lv3_min + 2).ToString() + ":N" + (lv3_min + 1 + lv3_x).ToString()).EntireRow.Insert(Excel.XlDirection.xlDown);
                                        }

                                        List<wf_BudgetItem> tmp_item = (from x in oThis.Current.ItemList
                                                                        where x.ItemID == Item.ItemID
                                                                        where x.ItemLevel == 3
                                                                        orderby x.Number
                                                                        select x).ToList();

                                        int tmp_i = 1;

                                        foreach (wf_BudgetItem tmp_a in tmp_item)
                                        {
                                            ws.get_Range("A" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemSN;
                                            ws.get_Range("B" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemName;
                                            ws.get_Range("D" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemCount;
                                            ws.get_Range("E" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemPrice;
                                            ws.get_Range("H" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemRem;
                                            tmp_i++;
                                        }

                                    }
                                }
                            }

                            ws.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);
                        }
                        else if(IsDelete)
                        {
                            ws.Delete();
                            i--;
                        }
                    }

                    book.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                    book.Save();
                    book.Close(false, Type.Missing, Type.Missing);
                }

                ws = null;
                book = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (book != null)
                    book.Close(false, Type.Missing, Type.Missing);

                if (app != null)
                    app.Quit();
                
                ws = null;
                book = null;
                app = null;

                GC.Collect();
            }

            List<Byte[]> lsByte = new List<byte[]>();

            FileStream fs = new FileStream(strTempPath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            lsByte.Add(bytes);
            System.IO.File.Delete(strTempPath);

            Response.BufferOutput = false;

            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(dicSourcePath[ProjectCategory]));
            foreach (Byte[] aByte in lsByte)
            {
                Response.BinaryWrite(aByte);
            }
            Response.Flush();
            Response.End();

            return new EmptyResult();
        }
        
        public ActionResult DownloadTemplate(String ProjectType, List<BudgetItem> lsThis, Boolean CanEdit)
        {
            String strTempPath = Server.MapPath(WebConfigurationManager.AppSettings["DownloadTmpPath"]) + Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            System.IO.File.Copy(Server.MapPath(WebConfigurationManager.AppSettings["TemplatePath"] + "XLS--机关经费.xls"), strTempPath);

            #region Get Data From DB

            String strType = String.Empty;
            String strYear = String.Empty;
            List<String> lsUser = new List<String>();
            String strUser = String.Empty;

            DBDataContext DB = new DBDataContext();
            DB.Connection.Open();

            IEnumerable<String> ieType = from x in DB.list_ProjcetTypes where x.ID == ProjectType.ToString() select x.Name;
            IEnumerable<md_User> ieUserAll = from x in DB.md_Users where x.Status select x;
            IEnumerable<String> ieYear = from x in DB.list_Years where x.IsUsed select x.Name;
            IEnumerable<String> ieUser = from x in DB.md_Users where x.DepartmentGuid == CurrentUser.Department.Department.Guid select x.UserName;

            if (ieType.Any()) { strType = ieType.First(); }
            if (ieYear.Any()) { strYear = ieYear.First(); }
            if (ieUser.Any()) { lsUser = ieUser.ToList(); }
            foreach (String aUser in lsUser) { strUser = strUser + aUser + ","; }
            if (strUser.Length > 0) { strUser = strUser.Substring(0, strUser.Length - 1); }

            DB.Connection.Close();

            #endregion

            Excel._Application app = null;
            Excel.Workbook book = null;
            Excel.Worksheet ws = null;
            ExcelUilityItem XlTools = new ExcelUilityItem();

            try
            {
                if (lsThis.Count > 0)
                {
                    Dictionary<String, String> lsYesOrNo = ExcelSheetItem.GetList_YN();
                    Dictionary<String, String> lsAdvenceStatus = ExcelSheetItem.GetList_AdvenceStatus();

                    app = new Excel.ApplicationClass();

                    app.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
                    app.DisplayAlerts = false;
                    book = app.Workbooks.Open(strTempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    book.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                    IEnumerator<BudgetItem> oThis = lsThis.GetEnumerator();

                    //for (int i = 1; i < lsThis.Count; i++)
                    //{
                    //    ws = (Excel.Worksheet)book.Worksheets.get_Item("Sheet1");
                    //    ws.Copy(Type.Missing, ws);
                    //}

                    ExcelVar Var = new ExcelVar((Excel.Worksheet)book.Worksheets.get_Item("Var"), app);

                    for (int i = 1; i <= book.Worksheets.Count; i++)
                    {
                        ws = (Excel.Worksheet)book.Worksheets.get_Item(i);

                        if (ws.Name.ToLower() == "welcome")
                            continue;

                        if (ws.Name.ToLower() == "var")
                        {
                            ws.get_Range("C8").Value2 = ProjectType;
                            ws.get_Range("C9").Value2 = "FALSE";
                            continue;
                        }

                        if (oThis.MoveNext())
                        {
                            ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                            #region Header Data Set

                            oThis.Current.C.Calculate(oThis.Current.Budget.Guid);

                            ws.get_Range("O1").Value2 = oThis.Current.C.abcde_lj - oThis.Current.C.abcde;
                            ws.get_Range("O2").Value2 = oThis.Current.C.x_lj - oThis.Current.C.x;

                            ws.get_Range("B1").Value2 = oThis.Current.Budget.Year;
                            ws.get_Range("C1").Value2 = String.Format(ws.get_Range("C1").Text.ToString(), strType);
                            ws.get_Range("B2").Value2 = oThis.Current.Project.Department.DepartmentName;
                            TemplateSheetItem.SetExcelCellValue(ws, "B3:F3", oThis.Current.Project.Project.ProjectName, CanEdit);
                            TemplateSheetItem.SetExcelCellValue(ws, "I3:J3", oThis.Current.Project.ProjcetPIC.UserName, CanEdit);

                            ws.get_Range("A5").Value2 = oThis.Current.C.IsA.Name;

                            if (oThis.Current.C.IsA.Value.ToUpper() == "TRUE")
                            {
                                ws.get_Range("B5").Value = oThis.Current.Budget.AdvencePlanAmount;
                                ws.get_Range("D5").Value = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvencePlanTime);
                                ws.get_Range("F5").Value = CommonItem.X_yyyyMMdd(oThis.Current.Budget.AdvenceReturnTime);
                                ws.get_Range("H5").Value = oThis.Current.AdvencePIC.UserName;
                                ws.get_Range("J5").Value = oThis.Current.C.x;
                                ws.get_Range("J6").Value = oThis.Current.Budget.AdvencetRemark;

                                ws.get_Range("A5").Locked = true;
                                ws.get_Range("A5").Interior.ColorIndex = 15;
                                ws.get_Range("A5").Validation.Delete();

                                ws.get_Range("B5:J5").Locked = false;
                                ws.get_Range("B5:J5").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                ws.get_Range("A9").Formula = "=SUM(J5,D6,H6)";
                            }
                            else
                            {
                                ws.get_Range("A5").Locked = false;
                                ws.get_Range("A5").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                ws.get_Range("B5:J5").Locked = true;
                                ws.get_Range("B5:J5").Interior.ColorIndex = 15;

                                ws.get_Range("A9").Formula = "=SUM(B9:I9)";
                            }

                            XlTools.AddList(ref ws, "H5:I5"
                                , (from x in ieUserAll where x.DepartmentGuid == oThis.Current.Project.Project.DepartmentGuid select x.UserName),
                                "请选择正确的负责人");

                            ws.get_Range("B6").Value = oThis.Current.C.AS.Name;
                            ws.get_Range("D6").Value = oThis.Current.C.y;
                            ws.get_Range("F6").Value = oThis.Current.C.z;
                            ws.get_Range("H6").Value = oThis.Current.C.f;

                            //ws.get_Range("B9:I9").Locked = true;
                            //ws.get_Range("B9:I9").Interior.ColorIndex = 15;
                            ws.get_Range("B9").Value = oThis.Current.Budget.Income_StandardFunds;
                            ws.get_Range("C9").Value = oThis.Current.Budget.Income_ProjectFunds;
                            ws.get_Range("D9").Value = oThis.Current.Budget.Income_DepartmentSubvention;
                            ws.get_Range("E9").Value = oThis.Current.Budget.Income_ScienceSubvention;
                            ws.get_Range("F9").Value = oThis.Current.Budget.Income_PoliticalSubvention;
                            ws.get_Range("G9").Value = oThis.Current.Budget.Income_MangerSubvention;
                            ws.get_Range("H9").Value = oThis.Current.Budget.Income_Other;
                            ws.get_Range("I9").Value = oThis.Current.Budget.Income_Balance;

                            ws.get_Range("J9").Value = oThis.Current.Budget.BudgetRemark;

                            ws.get_Range("G12").Value = oThis.Current.Budget.BudgetPay_Remark;

                            //ws.get_Range("A11").Value = oThis.Current.Budget.BudgetPay_Amount;
                            ws.get_Range("C11").Value = oThis.Current.Budget.ActualPay_Amount;
                            ws.get_Range("E11").Value = oThis.Current.Budget.His_Amount;
                            ws.get_Range("G11").Value = oThis.Current.Budget.BudgetRemark;

                            if (CanEdit)
                            {
                                ws.get_Range("D6").Locked = false;
                                ws.get_Range("D6").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

                                ws.get_Range("B2:I2").Locked = false;
                                ws.get_Range("B2:I2").Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
                            }

                            #endregion

                            #region Item Data Set

                            List<String> Lv3_ID = new List<string>();

                            foreach (wf_BudgetItem Item in oThis.Current.ItemList)
                            {
                                if (Item.ItemLevel == 3)
                                {
                                    Item.ItemID = "201";

                                    if (Lv3_ID.Contains(Item.ItemID))
                                        continue;
                                    else
                                    {
                                        Int32 lv3_count = Convert.ToInt32(app.WorksheetFunction.CountIf(ws.get_Range("P:P"), Item.ItemID));
                                        Int32 lv3_min = Convert.ToInt32(app.WorksheetFunction.Match(Convert.ToInt32(Item.ItemID), ws.get_Range("P:P"), 0));
                                        Int32 lv3_max = Convert.ToInt32(app.WorksheetFunction.Match(Convert.ToInt32(Item.ItemID), ws.get_Range("P:P"), 1));
                                        Int32 lv3_count_ = oThis.Current.ItemList.Count(o => o.ItemID == Item.ItemID);
                                        Int32 lv3_x = (lv3_count > lv3_count_) ? 0 : (lv3_count_ - lv3_count);

                                        if (lv3_x > 0)
                                        {
                                            ws.get_Range("P" + (lv3_min + 2).ToString()).EntireRow.Copy();
                                            ws.get_Range("P" + (lv3_min + 2).ToString() + ":P" + (lv3_min + 1 + lv3_x).ToString()).EntireRow.Insert(Excel.XlDirection.xlDown);
                                        }

                                        List<wf_BudgetItem> tmp_item = (from x in oThis.Current.ItemList
                                                                        where x.ItemID == Item.ItemID
                                                                        where x.ItemLevel == 3
                                                                        orderby x.Number
                                                                        select x).ToList();

                                        int tmp_i = 0;

                                        foreach (wf_BudgetItem tmp_a in tmp_item)
                                        {
                                            ws.get_Range("A" + (lv3_min + tmp_i).ToString()).Value2 = (tmp_i + 1).ToString();
                                            ws.get_Range("B" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemName;
                                            ws.get_Range("E" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemUnit;
                                            ws.get_Range("F" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemPrice;
                                            ws.get_Range("G" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemCount;
                                            ws.get_Range("J" + (lv3_min + tmp_i).ToString()).Value2 = tmp_a.ItemRem;
                                            tmp_i++;
                                        }

                                    }
                                }
                            }

                            #endregion

                            ws.Name = oThis.Current.Project.Project.ProjectCode;

                            Var.SetSheetVar(ws.CodeName, "PJCode", oThis.Current.Project.Project.ProjectCode);

                            ws.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);
                        }
                        else
                        {
                            ws.Delete();
                            i--;

                            //ws.Unprotect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                            //ws.get_Range("B1").Value2 = strYear;
                            //ws.get_Range("C1").Value2 = String.Format(ws.get_Range("C1").Text.ToString(), strType);
                            //ws.get_Range("B2").Value2 = CurrentUser.Department.Department.DepartmentName;

                            //ws.get_Range("I3:J3").Validation.Delete();
                            //ws.get_Range("I3:J3").Validation.Add(Excel.XlDVType.xlValidateList
                            //    , Excel.XlDVAlertStyle.xlValidAlertStop
                            //    , Excel.XlFormatConditionOperator.xlBetween
                            //    , strUser);
                            //ws.get_Range("I3:J3").Validation.IgnoreBlank = true;
                            //ws.get_Range("I3:J3").Validation.InCellDropdown = true;
                            //ws.get_Range("I3:J3").Validation.InputTitle = "";
                            //ws.get_Range("I3:J3").Validation.ErrorTitle = "";
                            //ws.get_Range("I3:J3").Validation.InputMessage = "";
                            //ws.get_Range("I3:J3").Validation.ErrorMessage = "请选择正确的科目负责人";
                            //ws.get_Range("I3:J3").Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            //ws.get_Range("I3:J3").Validation.ShowInput = true;
                            //ws.get_Range("I3:J3").Validation.ShowError = true;

                            //ws.get_Range("H5:I5").Validation.Delete();
                            //ws.get_Range("H5:I5").Validation.Add(Excel.XlDVType.xlValidateList
                            //    , Excel.XlDVAlertStyle.xlValidAlertStop
                            //    , Excel.XlFormatConditionOperator.xlBetween
                            //    , strUser);
                            //ws.get_Range("H5:I5").Validation.IgnoreBlank = true;
                            //ws.get_Range("H5:I5").Validation.InCellDropdown = true;
                            //ws.get_Range("H5:I5").Validation.InputTitle = "";
                            //ws.get_Range("H5:I5").Validation.ErrorTitle = "";
                            //ws.get_Range("H5:I5").Validation.InputMessage = "";
                            //ws.get_Range("H5:I5").Validation.ErrorMessage = "请选择正确的负责人";
                            //ws.get_Range("H5:I5").Validation.IMEMode = (int)Excel.XlIMEMode.xlIMEModeNoControl;
                            //ws.get_Range("H5:I5").Validation.ShowInput = true;
                            //ws.get_Range("H5:I5").Validation.ShowError = true;

                            //ws.get_Range("A9").Formula = "=SUM(B9:I9)";

                            //ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

                            //ws.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);
                        }
                    }

                    book.Protect(Password: WebConfigurationManager.AppSettings["ExcelPwd"]);

                    book.Save();
                    book.Close(false, Type.Missing, Type.Missing);
                }

                ws = null;
                book = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (book != null)
                    book.Close(false, Type.Missing, Type.Missing);

                if (app != null)
                    app.Quit();

                ws = null;
                book = null;
                app = null;

                GC.Collect();
            }

            List<Byte[]> lsByte = new List<byte[]>();

            FileStream fs = new FileStream(strTempPath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            lsByte.Add(bytes);
            System.IO.File.Delete(strTempPath);

            Response.BufferOutput = false;

            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("XLS--机关经费--" + strType + ".xls"));
            foreach (Byte[] aByte in lsByte)
            {
                Response.BinaryWrite(aByte);
            }
            Response.Flush();
            Response.End();

            return new EmptyResult();
        }

    }
}
